#-*- coding: utf-8 -*-
#coding = utf-8
import xlrd
import types
import xlwt
#导入二维费率表
flFileName='dfl'
flExl = xlrd.open_workbook(flFileName+'.xlsx')
flTable = flExl.sheets()[0]
flNrows = flTable.nrows
flNcols = flTable.ncols

zrArray = {u'不附加':[u'恶性肿瘤',u'1.被保险人于生效之日起 90 日内，确诊初次患本合同所指的恶性肿瘤，给付本保险实际交纳的保险费，本合同终止。2.被保险人于生效之日起 90 日后，确诊初次患本合同所指的恶性肿瘤，给付恶性肿瘤保险金额，本合同终止。'], u'附加':[u'特定恶性肿瘤',u'被保险人于生效之日起 90 日后，确诊初次患本合同所指的特定恶性肿瘤，除给付恶性肿瘤保险金外，还将给付特定恶性肿瘤保险金，本合同终止。 本合同所指的特定恶性肿瘤保障范围为： 1.男性被保险人：肺癌、肝癌、胃癌、前列腺癌；2.女性被保险人：肺癌、肝癌、胃癌、乳腺癌']}
sex=""
extr = ""
insureBaoe = ""

#数组后追加元素（返回的为新数组对象）
#@param arr 旧数组
#@param element 需要追加的元素
def arrAddElement(arr, element):
	result = []
	for i in range(0,len(arr)):
		result.append(arr[i])
	result.append(element)
	return result


#格式化年龄
def parseAge (age):
	arr = age.split('-')
	return arr[0]+';y'+'-'+ arr[1]+';y'

#格式化性别
def parseSex (sex):
	print(sex)
	# return sex == u'男' if u'1' else u'2'

	if(sex == u'男'):
		return parseValueStr(1)
	else:
		return parseValueStr(2)

#格式化保额
def parseBaoe (be):
	print(be)
	result = be
	if be.index(u'万') > -1:
		result = be.split(u'万')[0] + ';w'
	return result

#格式化value
def parseValueStr (val):
	if type(val) is types.IntType or type(val) is types.FloatType:
		return str(int(val))
	return val.strip()

def parseExtr(val):
	if(val == u'不附加'):
		return parseValueStr(0)
	else:
		return parseValueStr(1)


sumData = [] #整合后的表格数据
sumData2 = [] #整合后保障权益表

sumDataTh0 = [u'性别', u'投保年龄', u'附加', u'保额', u'保费']
sumDataTh = [u'insureSex', u'ageTerm', u'isImmunity', u'insuerBaoe', u'price']

sumData2Th0 = [u'性别', u'投保年龄', u'附加', u'保额', u'项目名', u'content']
sumData2Th = [u'insureSex', u'ageTerm', u'isImmunity', u'insuerBaoe', u'label', u'content']

colLab = [] #费率表列元素提取

rowsValue1 = flTable.row_values(0) #表第一行元素
rowsValue2 = flTable.row_values(1) #表第二行元素
#整合colArray
for i in range(3, len(rowsValue1)):
	obj = {}
	item1 = rowsValue1[i]
	item2 = rowsValue2[i]
	if len(item1.strip()) != 0:
		insureBaoe = item1
	obj["insureBaoe"] = insureBaoe
	obj["extr"] = item2
	colLab.append(obj)
#整合表格数据	
for i in range(2, flNrows):
	rowValues = flTable.row_values(i)
	print(rowValues)
	item1 = rowValues[1]
	age = rowValues[2]
	if(len(item1.strip()) != 0):
		sex = item1
	for j in range(3, len(rowValues)):
		jtem =parseValueStr(rowValues[j])
		if len(jtem.strip()) !=0:
			data = []
			data2= []
			data.append(parseSex(sex))  #性别sex
			data.append(parseAge(age))  #年龄age
			data.append(parseExtr(colLab[j-3]['extr'])) #附加 extr
			data.append(parseBaoe(colLab[j-3]['insureBaoe'])) #保额 insureBaoe
			data.append(jtem) #保费

			data2.append(parseSex(sex))
			data2.append(parseAge(age))
			data2.append(parseExtr(colLab[j-3]['extr'])) #附加 extr
			data2.append(parseBaoe(colLab[j-3]['insureBaoe'])) #保额 insureBaoe
			data2.append(zrArray[colLab[j-3]['extr']][0])#label
			data2.append(zrArray[colLab[j-3]['extr']][1])#content
			sumData.append(data)
			sumData2.append(data2)
print(sumData)
#导出excel
def set_style(name,height,bold=False):
  style = xlwt.XFStyle() # 初始化样式
 
  font = xlwt.Font() # 为样式创建字体
  font.name = name # 'Times New Roman'
  font.bold = bold
  font.color_index = 4
  font.height = height
 
  # borders= xlwt.Borders()
  # borders.left= 6
  # borders.right= 6
  # borders.top= 6
  # borders.bottom= 6
 
  style.font = font
  # style.borders = borders
 
  return style
fw = xlwt.Workbook()
sheet1 = fw.add_sheet(u'费率', cell_overwrite_ok = True)
for i in range(0, len(sumDataTh0)):
	sheet1.write(0, i, sumDataTh0[i], set_style('Times New Roman',220,True))

for i in range(0, len(sumDataTh)):
	print(sumDataTh[i])
	sheet1.write(1, i, sumDataTh[i], set_style('Times New Roman',220,True))

for i in range(0, len(sumData)):
	item = sumData[i]
	for j in range(0, len(item)):
		sheet1.write(i+2, j,item[j], set_style('Times New Roman',220,True))

sheet2 = fw.add_sheet(u'保障权益', cell_overwrite_ok = True)
for i in range(0, len(sumData2Th0)):
	sheet2.write(0, i,sumData2Th0[i], set_style('Times New Roman',220,True))

for i in range(0, len(sumData2Th)):
	sheet2.write(1, i,sumData2Th[i], set_style('Times New Roman',220,True))

for i in range(0, len(sumData2)):
	item = sumData2[i]
	for j in range(0, len(item)):
		sheet2.write(i+2, j, item[j], set_style('Times New Roman',220,True))
fw.save(flFileName+"_result.xls")
